﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Frunit.Service
{
    /// <summary>
    /// 主要水果生产情况季度统计表通用操作类
    /// </summary>
    public class FruProSeasonServices : FruProSeasons
    {
        public Pager<List<FruProSeasonEntity>> GetPageList(FruProSeasonQuery query)
        {
            Pager<List<FruProSeasonEntity>> obj = new Pager<List<FruProSeasonEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_FruProSeason where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<FruProSeasonEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取主要水果生产情况季度统计表分页列表失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<FruProSeasonEntity> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruProSeasonEntity> list = new List<FruProSeasonEntity>();
            try
            {

                FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
                switch (fileSuffix)
                {
                    case ".xls":
                        workbook = new HSSFWorkbook(fs);
                        break;
                    case ".xlsx":
                        workbook = new XSSFWorkbook(fs);
                        break;
                    default:
                        workbook = new HSSFWorkbook(fs);
                        break;
                }
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(2);//从第四行开始读
                    int cellCount = firstRow.LastCellNum;//获取列数

                    //获取所有数据的行数
                    int rowCount = 0;
                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            rowCount = sheet.GetRow(i).RowNum;
                        }
                        else
                        {
                            break;
                        }
                    }
                    for (int i = startRow; i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        var eet = sheet.GetRowEnumerator();
                        FruProSeasonEntity model = new FruProSeasonEntity();
                        model.Variety = row.GetCell(0).ToString();
                        if(row.GetCell(1) != null)
                        {
                            if (row.GetCell(1).CellType == CellType.Formula)
                            {
                                model.ZyArea = Decimal.Round(Convert.ToDecimal(row.GetCell(1).NumericCellValue), 1);//在园水果面积(亩）
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(1).ToString()))
                                {
                                    model.ZyArea = Convert.ToDecimal(row.GetCell(1).ToString());//在园水果面积(亩）
                                }
                                else
                                {
                                    model.ZyArea = null;
                                }

                            }
                        }
                       if(row.GetCell(2) != null)
                        {
                            if ( row.GetCell(2).CellType == CellType.Formula)
                            {
                                model.YshArea = Decimal.Round(Convert.ToDecimal(row.GetCell(2).NumericCellValue), 1);//已收获面积（亩）
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(2).ToString()))
                                {
                                    model.YshArea = Convert.ToDecimal(row.GetCell(2).ToString());//已收获面积（亩）
                                }
                                else
                                {
                                    model.YshArea = null;
                                }
                            }
                        }

                        if (row.GetCell(3) != null )
                        {
                            if (row.GetCell(3).CellType == CellType.Formula)
                            {
                                model.YshYield = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 1);//已收获产量
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(3).ToString()))
                                {
                                    model.YshYield = Convert.ToDecimal(row.GetCell(3).ToString());//已收获产量
                                }
                                else
                                {
                                    model.YshYield = null;
                                }
                            }
                        }

                        if (row.GetCell(4) != null )
                        {
                            if ( row.GetCell(4).CellType == CellType.Formula)
                            {
                                model.AvgPrice = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 2);
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(4).ToString()))
                                {
                                    model.AvgPrice = Convert.ToDecimal(row.GetCell(4).ToString());//季度平均地头批发价（元/公斤）
                                }
                                else
                                {
                                    model.AvgPrice = null;
                                }
                            }
                        }


                        if (row.GetCell(5) != null)
                        {
                            if ( row.GetCell(5).CellType == CellType.Formula)
                            {
                                model.AddArea = Decimal.Round(Convert.ToDecimal(row.GetCell(5).NumericCellValue), 1);//本季度新增面积（亩）
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(5).ToString()))
                                    model.AddArea = Convert.ToDecimal(row.GetCell(5).ToString());//本季度新增面积（亩）
                                else
                                {
                                    model.AddArea = null;
                                }
                            }
                        }
                        
                        if (row.GetCell(6) != null && row.GetCell(6).ToString() != null )
                        {
                            model.Remark = row.GetCell(6).ToString();
                        }
                        list.Add(model);
                    }
                }
                if (list.Count != 0)
                {
                    FruProSeasonEntity entity = list[list.Count - 1];
                    entity.ZyArea = 0;
                    entity.YshArea = 0;
                    entity.YshYield = 0;
                    entity.AddArea = 0;
                    
                    
                    for (int r = 0; r < list.Count - 1; r++)
                    {
                        if (r < 3 || r > 8)//非子项加和
                        {
                            if(list[r].ZyArea != null)
                            entity.ZyArea += list[r].ZyArea;
                            if (list[r].YshArea != null)
                                entity.YshArea += list[r].YshArea;
                            if (list[r].YshYield != null)
                                entity.YshYield += list[r].YshYield;
                            if (list[r].AddArea != null)
                                entity.AddArea += list[r].AddArea;
                        }                       
                    }                   
                    list[list.Count - 1] = entity;
                   
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.ToString().Trim());
            }
            return list;
        }

        public ResResult<List<ProSeasonStatisModel>> getStatisListData(string AreaCode, string FillInYear, string VarietyCode, string TimesCycle)
        {
            ResResult<List<ProSeasonStatisModel>> obj = new ResResult<List<ProSeasonStatisModel>>();
            List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                ProSeasonStatisModel totalModel = new ProSeasonStatisModel();
                totalModel.AreaName = "合计";
                totalModel.ZyArea = 0;
                totalModel.YshArea = 0;
                totalModel.YshYield = 0;
                totalModel.AddArea = 0;
                foreach (var code in citylist)
                {
                    ProSeasonStatisModel cityModel = getProvinStatis(code, FillInYear, VarietyCode, TimesCycle);
                    if (cityModel != null)
                    {
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        totalModel.ZyArea += cityModel.ZyArea;
                        totalModel.YshArea += cityModel.YshArea;
                        totalModel.YshYield += cityModel.YshYield;
                        totalModel.AddArea += cityModel.AddArea;
                    }
                    else
                    {
                        cityModel = new ProSeasonStatisModel();
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        cityModel.ZyArea = 0;
                        cityModel.YshArea = 0;
                        cityModel.YshYield = 0;
                        cityModel.AddArea = 0;
                    }
                    list.Add(cityModel);

                }
                list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getCityStatisList(AreaCode, FillInYear, VarietyCode, TimesCycle);
                    ProSeasonStatisModel cityModel = getProvinStatis(AreaCode, FillInYear, VarietyCode, TimesCycle);
                    if (cityModel == null)
                    {
                        cityModel = new ProSeasonStatisModel();
                        cityModel.ZyArea = 0;
                        cityModel.YshArea = 0;
                        cityModel.YshYield = 0;
                        cityModel.AddArea = 0;
                    }
                    cityModel.AreaName = "合计";
                    list.Add(cityModel);

                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear, VarietyCode, TimesCycle);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public ProSeasonStatisModel getProvinStatis(string AreaCode, string FillInYear, string VarietyCode, string TimesCycle)
        {
            ProSeasonStatisModel list = new ProSeasonStatisModel();
            string strSql = "select t3.Variety,";
            strSql += "isnull(sum(t3.ZyArea),0.00) ZyArea ,";
            strSql += "isnull(sum(t3.YshArea),0.00) YshArea ,";
            strSql += "isnull(sum(t3.YshYield),0.00) YshYield ,";
            strSql += "isnull(sum(t3.AddArea),0.00) AddArea ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSeason t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSeason' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += " and TimesCycle = @TimesCycle ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and VarietyCode = '" + VarietyCode + "' ";
            }
            strSql += " group by t3.Variety,t3.SortNum order by t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSeasonStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear, TimesCycle = TimesCycle }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果季度统计列表信息失败", ex);
            }
            return list;
        }
        public List<ProSeasonStatisModel> getCityStatisList(string AreaCode, string FillInYear, string VarietyCode, string TimesCycle)
        {
            List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "isnull(t3.ZyArea,0.00) ZyArea ,";
            strSql += "isnull(t3.YshArea,0.00) YshArea ,";
            strSql += "isnull(t3.YshYield,0.00) YshYield ,";
            strSql += "isnull(t3.AvgPrice,0.00) AvgPrice ,";
            strSql += "isnull(t3.AddArea,0.00) AddArea ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSeason t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSeason' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += " and TimesCycle = @TimesCycle ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t1.Code, t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSeasonStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear, TimesCycle = TimesCycle }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果季度统计列表信息失败", ex);
            }
            return list;
        }
        public List<ProSeasonStatisModel> getCountyStatisList(string AreaCode, string FillInYear, string VarietyCode, string TimesCycle)
        {
            List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "t3.ZyArea ZyArea ,";
            strSql += "t3.YshArea YshArea ,";
            strSql += "t3.YshYield YshYield ,";
            strSql += "t3.AvgPrice AvgPrice ,";
            strSql += "t3.AddArea AddArea ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSeason t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSeason' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += " and TimesCycle = @TimesCycle ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSeasonStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear, TimesCycle = TimesCycle }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果季度统计列表信息失败", ex);
            }
            return list;
        }

        /// <summary>
        /// 获取全省的各个类型的数据
        /// </summary>
        /// <param name="FillInYear"></param>
        /// <param name="VarietyCode"></param>
        /// <param name="TimesCycle"></param>
        /// <returns></returns>
        public ProSeasonStatisModel getTProvinStatis(string FillInYear, string VarietyCode, string TimesCycle)
        {
            ProSeasonStatisModel list = new ProSeasonStatisModel();
            string strSql = "select t3.Variety,";
            strSql += "isnull(sum(t3.ZyArea),0.00) ZyArea ,";
            strSql += "isnull(sum(t3.YshArea),0.00) YshArea ,";
            strSql += "isnull(sum(t3.YshYield),0.00) YshYield ,";
            strSql += "isnull(sum(t3.AddArea),0.00) AddArea ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSeason t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSeason' and t1.ParentCode like @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += " and TimesCycle = @TimesCycle ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and VarietyCode = '" + VarietyCode + "' ";
            }
            strSql += " group by t3.Variety,t3.SortNum order by t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSeasonStatisModel>(strSql, new { AreaCode = "%53%", FillInYear = FillInYear, TimesCycle = TimesCycle }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果季度统计列表信息失败", ex);
            }
            return list;
        }

        public List<ProSeasonStatisModel> getAllCountyStatisList(string FillInYear, string VarietyCode, string TimesCycle)
        {
            List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "isnull(t3.ZyArea,0.00) ZyArea ,";
            strSql += "isnull(t3.YshArea,0.00) YshArea ,";
            strSql += "isnull(t3.YshYield,0.00) YshYield ,";
            strSql += "isnull(t3.AvgPrice,0.00) AvgPrice ,";
            strSql += "isnull(t3.AddArea,0.00) AddArea ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSeason t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSeason' and t1.ParentCode !='530000000000'  and t2.FillInYear = @FillInYear ";
            strSql += " and TimesCycle = @TimesCycle ";
            strSql += " and t1.ParentCode is not null  ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t1.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSeasonStatisModel>(strSql, new { FillInYear = FillInYear, TimesCycle = TimesCycle }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果季度统计列表信息失败", ex);
            }
            return list;
        }

        /// <summary>
        /// 导出汇总
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <param name="VarietyCode"></param>
        /// <param name="TimesCycle"></param>
        /// <returns></returns>
        public ResResult<List<ProSeasonStatisModel>> getOutStatisData(string AreaCode, string FillInYear, string VarietyCode, string TimesCycle)
        {
            ResResult<List<ProSeasonStatisModel>> obj = new ResResult<List<ProSeasonStatisModel>>();
            List<ProSeasonStatisModel> list = new List<ProSeasonStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                ProSeasonStatisModel totalModel = new ProSeasonStatisModel();
                totalModel.AreaName = "合计";
                totalModel.ZyArea = 0;
                totalModel.YshArea = 0;
                totalModel.YshYield = 0;
                totalModel.AddArea = 0;
                totalModel.tag = 2;
                foreach (var code in citylist)
                {
                    list.AddRange(getCityStatisList(code, FillInYear, VarietyCode, TimesCycle));
                    ProSeasonStatisModel cityModel = getProvinStatis(code, FillInYear, VarietyCode, TimesCycle);
                    if (cityModel != null)
                    {
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        totalModel.ZyArea += cityModel.ZyArea;
                        totalModel.YshArea += cityModel.YshArea;
                        totalModel.YshYield += cityModel.YshYield;
                        totalModel.AddArea += cityModel.AddArea;
                    }
                    else
                    {
                        cityModel = new ProSeasonStatisModel();
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        cityModel.ZyArea = 0;
                        cityModel.YshArea = 0;
                        cityModel.YshYield = 0;
                        cityModel.AddArea = 0;
                    }
                    cityModel.tag = 1;
                    list.Add(cityModel);

                }
                list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getCityStatisList(AreaCode, FillInYear, VarietyCode, TimesCycle);
                    ProSeasonStatisModel cityModel = getProvinStatis(AreaCode, FillInYear, VarietyCode, TimesCycle);
                    if (cityModel == null)
                    {
                        cityModel = new ProSeasonStatisModel();
                        cityModel.ZyArea = 0;
                        cityModel.YshArea = 0;
                        cityModel.YshYield = 0;
                        cityModel.AddArea = 0;
                    }
                    cityModel.AreaName = "合计";
                    list.Add(cityModel);

                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear, VarietyCode, TimesCycle);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }


    }
}